package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmswms.entity.WhsStorageInventory;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 储位库存信息表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-03-18
 */
@Mapper
public interface WhsStorageInventoryMapper extends BaseMapper<WhsStorageInventory> {

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "    LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "    LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID\n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL AND WSI.BOX_NOTE IN (" +
            "    SELECT DISTINCT WHSLOG.BOX_NOTE\n" +
            "    FROM WHS_OPERATE_LOG WHSLOG\n" +
            "             LEFT JOIN WHS_STORAGE WHSSTORAGE ON WHSLOG.UUID_STORAGE = WHSSTORAGE.UUID\n" +
            "    WHERE WHSLOG.UUID_STORAGE IS NOT NULL\n" +
            "      AND WHSLOG.BOX_NOTE IS NOT NULL\n" +
            "      AND WHSLOG.DOCUMENT_TYPE = '2'\n" +
            "      AND WHSSTORAGE.WERKS = #{werks}\n" +
            "      AND WHSSTORAGE.WHS_LOCATION_CODE = #{whsLocationCode}\n" +
            "      AND WHSLOG.CREATE_TIME BETWEEN TO_DATE(#{startDate}, 'yyyy-MM-dd hh24:mi:ss') AND TO_DATE(#{endDate}, 'yyyy-MM-dd hh24:mi:ss')\n" +
            ")\n" +
            "</script>")
    List<Map<String, Object>> getStorageXByDate(@Param("werks") String werks,
                                                @Param("whsLocationCode") String whsLocationCode,
                                                @Param("startDate") String startDate,
                                                @Param("endDate") String endDate
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "    LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "    LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID\n" +
            "WHERE WSI.QUANTITY>0 AND WHS.WERKS = #{werks} AND WHS.WHS_LOCATION_CODE = #{whsLocationCode} \n" +
            "   AND WSI.MATERIAL_NO IS NOT NULL AND WSI.MATERIAL_NO IN (" +
            "    SELECT DISTINCT WHSLOG.MATERIAL_NO\n" +
            "    FROM WHS_OPERATE_LOG WHSLOG\n" +
            "             LEFT JOIN WHS_STORAGE WHSSTORAGE ON WHSLOG.UUID_STORAGE = WHSSTORAGE.UUID\n" +
            "    WHERE WHSLOG.UUID_STORAGE IS NOT NULL\n" +
            "      AND WHSLOG.MATERIAL_NO IS NOT NULL\n" +
            "      AND WHSLOG.DOCUMENT_TYPE = '2'\n" +
            "      AND WHSSTORAGE.WERKS = #{werks}\n" +
            "      AND WHSSTORAGE.WHS_LOCATION_CODE = #{whsLocationCode}\n" +
            "      AND WHSLOG.CREATE_TIME BETWEEN TO_DATE(#{startDate}, 'yyyy-MM-dd hh24:mi:ss') AND TO_DATE(#{endDate}, 'yyyy-MM-dd hh24:mi:ss')\n" +
            ")\n" +
            "</script>")
    List<Map<String, Object>> getStorageMByDate(@Param("werks") String werks,
                                                @Param("whsLocationCode") String whsLocationCode,
                                                @Param("startDate") String startDate,
                                                @Param("endDate") String endDate
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "    LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "    LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID\n" +
            "WHERE WSI.QUANTITY>0 AND WHS.WERKS = #{werks} AND WHS.WHS_LOCATION_CODE = #{whsLocationCode} \n" +
            "   AND WSI.MATERIAL_NO IS NOT NULL AND WSI.MATERIAL_NO IN (" +
            "    SELECT DISTINCT WHSLOG.MATERIAL_NO\n" +
            "    FROM WHS_OPERATE_LOG WHSLOG\n" +
            "             LEFT JOIN WHS_STORAGE WHSSTORAGE ON WHSLOG.UUID_STORAGE = WHSSTORAGE.UUID\n" +
            "    WHERE WHSLOG.UUID_STORAGE IS NOT NULL\n" +
            "      AND WHSLOG.MATERIAL_NO IS NOT NULL\n" +
            "      AND WHSLOG.DOCUMENT_TYPE = #{documentType}\n" +
            "      AND WHSSTORAGE.WERKS = #{werks}\n" +
            "      AND WHSSTORAGE.WHS_LOCATION_CODE = #{whsLocationCode}\n" +
            "      AND WHSLOG.CREATE_TIME BETWEEN TO_DATE(#{startDate}, 'yyyy-MM-dd hh24:mi:ss') AND TO_DATE(#{endDate}, 'yyyy-MM-dd hh24:mi:ss')\n" +
            ")\n" +
            "</script>")
    List<Map<String, Object>> getStorageByDate(@Param("werks") String werks,
                                                @Param("whsLocationCode") String whsLocationCode,
                                                @Param("startDate") String startDate,
                                                @Param("endDate") String endDate,
                                                @Param("documentType") String documentType
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  WSI.CREATE_TIME AS \"createTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryX(@Param("werks") List<String> werks,
                                                      @Param("whsLocationCode") List<String> whsLocationCode,
                                                      @Param("storageArea") List<String> storageArea,
                                                      @Param("shelfNo") List<String> shelfNo,
                                                      @Param("shelfLayer") List<String> shelfLayer,
                                                      @Param("shelfCell") List<String> shelfCell,
                                                      @Param("storageNo") List<String> storageNo,
                                                      @Param("boxNote") List<String> boxNote
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  WSI.CREATE_TIME AS \"createTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryXX(@Param("werks") List<String> werks,
                                                      @Param("whsLocationCode") List<String> whsLocationCode,
                                                      @Param("storageArea") List<String> storageArea,
                                                      @Param("shelfNo") List<String> shelfNo,
                                                      @Param("shelfLayer") List<String> shelfLayer,
                                                      @Param("shelfCell") List<String> shelfCell,
                                                      @Param("storageNo") List<String> storageNo,
                                                      @Param("boxNote") List<String> boxNote
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.MATERIAL_NO IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryM(@Param("werks") List<String> werks,
                                                      @Param("whsLocationCode") List<String> whsLocationCode,
                                                      @Param("storageArea") List<String> storageArea,
                                                      @Param("shelfNo") List<String> shelfNo,
                                                      @Param("shelfLayer") List<String> shelfLayer,
                                                      @Param("shelfCell") List<String> shelfCell,
                                                      @Param("storageNo") List<String> storageNo,
                                                      @Param("materialNo") List<String> materialNo
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventory(@Param("werks") List<String> werks,
                                                     @Param("whsLocationCode") List<String> whsLocationCode,
                                                     @Param("storageArea") List<String> storageArea,
                                                     @Param("shelfNo") List<String> shelfNo,
                                                     @Param("shelfLayer") List<String> shelfLayer,
                                                     @Param("shelfCell") List<String> shelfCell,
                                                     @Param("storageNo") List<String> storageNo,
                                                     @Param("materialNo") List<String> materialNo,
                                                     @Param("boxNote") List<String> boxNote
    );

    @Select("SELECT AVAILABLE_QTY FROM WHS_STORAGE_INVENTORY " +
            "WHERE UUID = #{storageUuid} " +
            "   AND BOX_NOTE = #{boxNote} AND AVAILABLE_QTY != 0 ")
    String getAvailableQtyByUuidBoxNote(@Param("storageUuid") String storageUuid, @Param("boxNote") String boxNote);

    @Select("SELECT ROWNUM AS \"rownum\",\n" +
            "       WSI.UUID AS \"uuid\",\n" +
            "       MATERIAL_NO AS \"materialNo\",\n" +
            "       QUANTITY AS \"quantity\",\n" +
            "       AVAILABLE_QTY AS \"availableQty\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "WHERE WSI.UUID = #{storageUuid,jdbcType=VARCHAR} \n" +
            "  AND WSI.MATERIAL_NO = #{materialNo,jdbcType=VARCHAR} \n" +
            "  AND WSI.AVAILABLE_QTY > 0 AND rownum = 1 \n")
    Map<String, Object> getWhsStorageInventoryByUuidMaterialNo(@Param("storageUuid") String storageUuid,
                                                               @Param("materialNo") String materialNo);

    @Select("<script>" +
            "SELECT COUNT(*) FROM WHS_STORAGE_INVENTORY WHS LEFT JOIN WHS_STORAGE WHSTORAGE ON WHS.UUID=WHSTORAGE.UUID \n" +
            "   WHERE WHS.BOX_NOTE= #{boxNote} \n" +
            "      <when test='werks!=null'> AND WHSTORAGE.WERKS = #{werks} </when> \n" +
            "      <when test='whsLocationCode!=null'> AND WHSTORAGE.WHS_LOCATION_CODE = #{whsLocationCode} </when> \n" +
            "</script>")
    int validateBoxNote(@Param("boxNote") String boxNote,@Param("werks") String werks,@Param("whsLocationCode") String whsLocationCode);


    @Select("<script>"+
            "SELECT BOX_NOTE AS \"boxNote\",\n"+
    "CREATE_TIME AS \"createTime\" FROM WHS_STORAGE_INVENTORY WHS WHERE WHS.BOX_NOTE IS NOT NULL \n"+
            "<when test='boxNote!=null'>AND WHS.BOX_NOTE = #{boxNote} </when> \n"+
    "</script>")
    List<Map<String, Object>> pageMaDataMap(Page<Map<String, Object>> pages,
                                            @Param("boxNote") String boxNote,
                                            @Param("werks") List<String> werks,
                                            @Param("whsLocationCode") List<String> whsLocationCode,
                                            @Param("storageArea") List<String> storageArea,
                                            @Param("shelfNo") List<String> shelfNo,
                                            @Param("shelfLayer") List<String> shelfLayer,
                                            @Param("shelfCell") List<String> shelfCell,
                                            @Param("storageNo") List<String> storageNo,
                                            @Param("materialNo") List<String> materialNo
);

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> pageDataMap(Page<Map<String, Object>> pages,
//                                            @Param("boxNote") String boxNote,
                                            @Param("werks") List<String> werks,
                                            @Param("whsLocationCode") List<String> whsLocationCode,
                                            @Param("storageArea") List<String> storageArea,
                                            @Param("shelfNo") List<String> shelfNo,
                                            @Param("shelfLayer") List<String> shelfLayer,
                                            @Param("shelfCell") List<String> shelfCell,
                                            @Param("storageNo") List<String> storageNo,
                                            @Param("materialNo") List<String> materialNo,
                                            @Param("boxNote") List<String> boxNote
    );




    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 \n"+
            "   <when test='werks!=null'> AND WHS.WERKS = #{werks} </when> \n" +
            "   <when test='whsLocationCode!=null'> AND WHS.WHS_LOCATION_CODE = #{whsLocationCode} </when> \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWSInventory(@Param("werks") String werks,
                                                     @Param("whsLocationCode") String whsLocationCode,
                                                     @Param("materialNo") List<String> materialNo,
                                                     @Param("boxNote") List<String> boxNote
    );


    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.MATERIAL_NO IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryMPage(Page<Map<String, Object>> pages, @Param("werks") List<String> werks,
                                                      @Param("whsLocationCode") List<String> whsLocationCode,
                                                      @Param("storageArea") List<String> storageArea,
                                                      @Param("shelfNo") List<String> shelfNo,
                                                      @Param("shelfLayer") List<String> shelfLayer,
                                                      @Param("shelfCell") List<String> shelfCell,
                                                      @Param("storageNo") List<String> storageNo,
                                                      @Param("materialNo") List<String> materialNo
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  WSI.CREATE_TIME AS \"createTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryXPage(Page<Map<String, Object>> pages, @Param("werks") List<String> werks,
                                                      @Param("whsLocationCode") List<String> whsLocationCode,
                                                      @Param("storageArea") List<String> storageArea,
                                                      @Param("shelfNo") List<String> shelfNo,
                                                      @Param("shelfLayer") List<String> shelfLayer,
                                                      @Param("shelfCell") List<String> shelfCell,
                                                      @Param("storageNo") List<String> storageNo,
                                                      @Param("boxNote") List<String> boxNote
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryPage(Page<Map<String, Object>> pages, @Param("werks") List<String> werks,
                                                     @Param("whsLocationCode") List<String> whsLocationCode,
                                                     @Param("storageArea") List<String> storageArea,
                                                     @Param("shelfNo") List<String> shelfNo,
                                                     @Param("shelfLayer") List<String> shelfLayer,
                                                     @Param("shelfCell") List<String> shelfCell,
                                                     @Param("storageNo") List<String> storageNo,
                                                     @Param("materialNo") List<String> materialNo,
                                                     @Param("boxNote") List<String> boxNote
    );

    @Select("SELECT SUM(QUANTITY) AS total, TO_CHAR(CREATE_TIME,'yyyy-mm-dd') AS incomingDate, TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd')-TO_DATE(TO_CHAR(CREATE_TIME,'yyyy-mm-dd'),'yyyy-mm-dd') as diff FROM WHS_STORAGE_INVENTORY GROUP BY TO_CHAR(CREATE_TIME,'yyyy-mm-dd') ORDER BY TO_CHAR(CREATE_TIME,'yyyy-mm-dd');")
    List<Map<String, Object>> getAge();
}
